﻿using Model;
using System.Data;

namespace BLL
{
    public class Sys_RoleGroupBLL : BaseBLL
    {
        public DataTable GetAllInfo()
        {
            string sql = string.Format(@"SELECT sysrg.rgro_id,
       sysrg.rgro_name,
       sysrg.role_id,
       CASE sysrg.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM SYS_ROLEGROUP sysrg
 ORDER BY sysrg.rgro_id");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfoRole()
        {
            string sql = @"SELECT T1.Rgro_Id,
       T1.RGRO_NAME,
       T1.ROLE_ID,
       listagg(T2.ROLE_NAME, ',') within GROUP(order by T1.Rgro_Id) AS ROLE_NAMES,
       CASE T1.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM sys_rolegroup T1
 INNER JOIN sys_role T2
    ON ',' || T1.ROLE_ID || ',' LIKE '%,' || to_char(T2.ROLE_ID) || ',%'
 group by t1.Rgro_Id, T1.RGRO_NAME, T1.ROLE_ID, T1.rgro_state";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfo1(int RGROID)
        {
            string sql = string.Format(@"SELECT sysrg.rgro_id,
       sysrg.rgro_name,
       sysrg.role_id,
       sysr.role_name,
       CASE sysrg.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM SYS_ROLEGROUP sysrg, SYS_ROLE sysr
 WHERE ',' || sysrg.role_id || ',' LIKE '%,' || sysr.role_id || ',%'
  AND RGRO_ID = {0}
 ORDER BY sysrg.rgro_id", RGROID);
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfo2()
        {
            string sql = string.Format(@"SELECT sysrg.rgro_id,
       sysrg.rgro_name,
       sysrg.role_id,
       sysr.role_name,
       CASE sysrg.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM SYS_ROLEGROUP sysrg, SYS_ROLE sysr
 WHERE ',' || sysrg.role_id || ',' LIKE '%,' || sysr.role_id || ',%'
   AND sysrg.rgro_state = '1'
 ORDER BY sysrg.rgro_id");
            DataTable dt = Query(sql);
            return dt;
        }

        public string GetNamebyRoleGroupId(string rgroupId)
        {
            string sql = string.Format(@"SELECT sysr.role_name, sysr.role_id
  FROM SYS_ROLEGROUP sysrg, SYS_ROLE sysr
 WHERE ',' || sysrg.role_id || ',' LIKE '%,' || sysr.role_id || ',%'
   AND sysrg.rgro_state = '1'
   AND ',' || '{0}' || ',' LIKE '%,' || sysrg.rgro_id || ',%'
 ORDER BY sysrg.rgro_id
", rgroupId);

            DataTable dt = Query(sql);

            string names = "";
            string ids = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    names += item[0].ToString() + ",";
                    ids += item[1].ToString() + ",";
                }
                names = ids.Substring(0, +ids.LastIndexOf(',')) + "%" + names.Substring(0, names.LastIndexOf(','));
            }

            if (names.Length > 0)
            {
                names = names.Substring(0, names.Length);
            }

            return names;
        }

        public Sys_RoleGroup GetNamebyRoleGroupId2(string rgroupId)
        {
            string sql = string.Format(@"SELECT sysr.role_name, sysr.role_id
  FROM SYS_ROLEGROUP sysrg, SYS_ROLE sysr
 WHERE ',' || sysrg.role_id || ',' LIKE '%,' || sysr.role_id || ',%'
   AND sysrg.rgro_state = '1'
   AND ',' || '{0}' || ',' LIKE '%,' || sysrg.rgro_id || ',%'
 ORDER BY sysrg.rgro_id
", rgroupId);

            DataTable dt = Query(sql);
            Sys_RoleGroup model = new Sys_RoleGroup();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.RGRO_ID = decimal.Parse(dt.Rows[0]["RGRO_ID"].ToString());
                model.RGRO_NAME = dt.Rows[0]["RGRO_NAME"].ToString();
                model.RGRO_STATE = dt.Rows[0]["RGRO_STATE"].ToString();
                model.ROLE_ID = dt.Rows[0]["ROLE_ID"].ToString();
            }
            return model;
        }

        public Sys_RoleGroup GetRoleGroupById(string id)
        {
            string sql = string.Format(@"SELECT sysrg.rgro_id,
       sysrg.rgro_name,
       sysrg.role_id,
       CASE sysrg.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM SYS_ROLEGROUP sysrg
 WHERE sysrg.rgro_id = {0}
 ORDER BY sysrg.rgro_id", id);

            DataTable dt = Query(sql);
            Sys_RoleGroup model = new Sys_RoleGroup();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.RGRO_ID = decimal.Parse(dt.Rows[0]["RGRO_ID"].ToString());
                model.RGRO_NAME = dt.Rows[0]["RGRO_NAME"].ToString();
                model.RGRO_STATE = dt.Rows[0]["RGRO_STATE"].ToString();
                model.ROLE_ID = dt.Rows[0]["ROLE_ID"].ToString();
            }
            return model;
        }

        public string GetNamebyRoleId(string rolesId, string rolesName)
        {
            string sql = string.Format(@"SELECT sysr.role_name, sysr.role_id
  FROM SYS_ROLE sysr
 WHERE --sysr.role_state = '1' AND
    ',' || '{0}' || ',' LIKE '%,' || sysr.role_id || ',%'
   AND ',' || '{1}' || ',' LIKE '%,' || sysr.role_name || ',%'
 ORDER BY sysr.role_id", rolesId, rolesName);

            DataTable dt = Query(sql);

            string names = "";
            string ids = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    names += item[0].ToString() + ",";
                    ids += item[1].ToString() + ",";
                }
                names = ids.Substring(0, +ids.LastIndexOf(',')) + "%" + names.Substring(0, names.LastIndexOf(','));
            }

            if (names.Length > 0)
            {
                names = names.Substring(0, names.Length);
            }

            return names;
        }

        public DataTable GetInfoByCondition(string Type, object param)
        {
            string sql = string.Format(@"SELECT sysrg.rgro_id,
       sysrg.rgro_name,
       sysrg.role_id,
       sysr.role_name,
       CASE sysrg.rgro_state
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as rgro_state
  FROM SYS_ROLEGROUP sysrg, SYS_ROLE sysr
 WHERE ',' || sysrg.role_id || ',' LIKE '%,' || sysr.role_id || ',%'");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND sysr.ROLE_NAME like '%" + str[0] + "%'";
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND sysrg.ROLE_STATE = " + str[1];
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND sysrg.rgro_name = " + str[2];
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND sysrg.rgro_id IN(" + param.ToString() + ")";
            }
            sql += " ORDER BY sysrg.rgro_id";

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetInfoByCondition2(string Type, object param)
        {
            string sql = string.Format(@" SELECT
    sysrg.rgro_id,
    sysrg.rgro_name,
    sysrg.role_id,
    CASE sysrg.rgro_state
    WHEN '0' THEN '禁用'
    WHEN '1' THEN '使用'
    END as rgro_state
FROM
    sys_rolegroup sysrg
WHERE 1=1");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND sysrg.rgro_name = '" + str[0] + "'";
                }
                if (str[1] != "-1")
                {
                    if (!string.IsNullOrEmpty(str[1]))
                    {
                        sql += " AND sysrg.rgro_state = " + str[1];
                    }
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND sysrg.rgro_id IN(" + param.ToString() + ")";
            }
            sql += " ORDER BY sysrg.rgro_id";

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetInfoByCondition3(string Type, object param)
        {
            string sql = @"SELECT *
  FROM (SELECT T1.Rgro_Id,
               T1.RGRO_NAME,
               T1.ROLE_ID,
               listagg(T2.ROLE_NAME, ',') within GROUP(order by T1.Rgro_Id) AS ROLE_NAMES,
               CASE T1.rgro_state
                 WHEN '0' THEN
                  '禁用'
                 WHEN '1' THEN
                  '使用'
               END as rgro_state
          FROM sys_rolegroup T1
         INNER JOIN sys_role T2
            ON ',' || T1.ROLE_ID || ',' LIKE
               '%,' || to_char(T2.ROLE_ID) || ',%'
         group by t1.Rgro_Id, T1.RGRO_NAME, T1.ROLE_ID, T1.rgro_state) SYSTABLE
 where 1 = 1";
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND rgro_name like '%" + str[0] + "%'";
                }
                if (str[1] != "请选择")
                {
                    if (!string.IsNullOrEmpty(str[1]))
                    {
                        sql += " AND rgro_state = '" + str[1] + "'";
                    }
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND rgro_id IN(" + param.ToString() + ")";
            }
            sql += " ORDER BY rgro_id";

            DataTable dt = Query(sql);
            return dt;
        }

        public bool INSERT(Sys_RoleGroup model, string operName)
        {
            //获取序列
            model.RGRO_ID = GetSequence("SEQ_Sys_RoleGroup");

            string sql = string.Format(@"INSERT INTO Sys_RoleGroup(RGRO_ID,RGRO_NAME,ROLE_ID,RGRO_STATE) VALUES('{0}','{1}','{2}','{3}')", model.RGRO_ID, model.RGRO_NAME, model.ROLE_ID, model.RGRO_STATE);
            int count = NoQuery(sql, operName);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UPDATE(Sys_RoleGroup model, string operName)
        {
            string sql = string.Format(@"UPDATE Sys_RoleGroup SET
Sys_RoleGroup.RGRO_NAME='{0}',
Sys_RoleGroup.ROLE_ID='{1}',
Sys_RoleGroup.RGRO_STATE='{2}'
WHERE Sys_RoleGroup.RGRO_ID={3}
", model.RGRO_NAME, model.ROLE_ID, model.RGRO_STATE, model.RGRO_ID);
            int count = NoQuery(sql, operName);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int DELETE(string ids, string operName)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 0;
            }
            string sql = string.Format(@"DELETE FROM Sys_RoleGroup WHERE Sys_RoleGroup.RGRO_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public Sys_RoleGroup Find(decimal id)
        {
            return GetRoleGroupById(id.ToString().Trim());
        }

        public int GetRecordCount(string Type, object param)
        {
            string sql = string.Format(@"SELECT count(1)
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY Rgro_Id asc) as r,
               T1.Rgro_Id,
               T1.RGRO_NAME,
               T1.ROLE_ID,
               listagg(T2.ROLE_NAME, ',') within GROUP(order by T1.Rgro_Id) AS ROLE_NAMES,
               CASE T1.rgro_state
                 WHEN '0' THEN
                  '禁用'
                 WHEN '1' THEN
                  '使用'
               END as rgro_state
          FROM sys_rolegroup T1
         INNER JOIN sys_role T2
            ON ',' || T1.ROLE_ID || ',' LIKE
               '%,' || to_char(T2.ROLE_ID) || ',%'
         group by t1.Rgro_Id, T1.RGRO_NAME, T1.ROLE_ID, T1.rgro_state) SYSTABLE
 where 1 = 1 ");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND rgro_name like '%" + str[0] + "%'";
                }
                if (str[1] != "请选择")
                {
                    if (!string.IsNullOrEmpty(str[1]))
                    {
                        sql += " AND rgro_state = '" + str[1] + "'";
                    }
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND rgro_id IN(" + param.ToString() + ")";
            }
            return GetCount(sql);
        }

        public DataTable GetListByPage(string Type, object param, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM(SELECT *
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               T1.Rgro_Id,
               T1.RGRO_NAME,
               T1.ROLE_ID,
               listagg(T2.ROLE_NAME, ',') within GROUP(order by T1.Rgro_Id) AS ROLE_NAMES,
               CASE T1.rgro_state
                 WHEN '0' THEN
                  '禁用'
                 WHEN '1' THEN
                  '使用'
               END as rgro_state
          FROM sys_rolegroup T1
         INNER JOIN sys_role T2
            ON ',' || T1.ROLE_ID || ',' LIKE
               '%,' || to_char(T2.ROLE_ID) || ',%'
         group by t1.Rgro_Id, T1.RGRO_NAME, T1.ROLE_ID, T1.rgro_state) SYSTABLE
 where 1 = 1 ");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND rgro_name like '%" + str[0] + "%'";
                }
                if (str[1] != "请选择")
                {
                    if (!string.IsNullOrEmpty(str[1]))
                    {
                        sql += " AND rgro_state = '" + str[1] + "'";
                    }
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND rgro_id IN(" + param.ToString() + ")";
            }
            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql);
        }
    }
}